from flask import Blueprint, jsonify, request, Response
from models import Product
from extensions import db
from flask_login import login_required
import csv
from io import StringIO
from sqlalchemy import text

products_bp = Blueprint('products', __name__)


@products_bp.route('/products', methods=['GET'])
@login_required
def get_products():
    try:
        products = Product.query.all()
        serialized_products = [product.to_dict() for product in products]
        return jsonify(serialized_products), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 400


@products_bp.route('/products/<int:id>', methods=['GET'])
@login_required
def get_product(id):
    try:
        product = Product.query.get_or_404(id)
        serialized_product = product.to_dict()
        return jsonify(serialized_product), 200
    except Exception as e:
        return jsonify({'error': '商品编号不存在'}), 400


@products_bp.route('/products', methods=['POST'])
@login_required
def create_product():
    try:
        data = request.get_json()
        existing_product = Product.query.get(data.get('product_id'))
        if existing_product:
            return jsonify({'error': '该商品编号已存在，请勿重复添加'}), 400

        new_product = Product(
            id=data.get('product_id'),
            name=data.get('product_name'),
            price=data.get('product_price'),
            supplier_id=data.get('supplier_id'),
            description=data.get('product_description'),
            remark=data.get('remark')
        )
        db.session.add(new_product)
        db.session.commit()
        return jsonify(new_product.to_dict()), 201
    except Exception as e:
        return jsonify({'error': str(e)}), 400


@products_bp.route('/products/<int:id>', methods=['PUT'])
@login_required
def update_product(id):
    try:
        product = Product.query.get_or_404(id)
        data = request.get_json()

        new_product_id = data.get('new_product_id')
        if new_product_id and int(new_product_id) != product.id:
            if Product.query.get(new_product_id):
                return jsonify({'error': '新商品编号已存在，请选择不同的编号。'}), 400
            product.id = int(new_product_id)

        product.name = data.get('product_name')
        product.price = data.get('product_price')
        product.supplier_id = data.get('supplier_id')
        product.description = data.get('product_description')
        product.remark = data.get('remark')

        db.session.commit()
        return jsonify(product.to_dict()), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 400


@products_bp.route('/products/<int:id>', methods=['DELETE'])
@login_required
def delete_product(id):
    try:
        product = Product.query.get_or_404(id)
        db.session.delete(product)
        db.session.commit()
        return jsonify({'message': '商品已删除'}), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 400


@products_bp.route('/products/export/csv', methods=['GET'])
@login_required
def export_products_csv():
    try:
        products = Product.query.all()
        output = StringIO()
        writer = csv.writer(output)
        writer.writerow(['id', 'name', 'price', 'supplier_id', 'description', 'remark'])
        for product in products:
            writer.writerow([
                product.id, product.name, product.price, product.supplier_id,
                product.description, product.remark
            ])
        output.seek(0)
        return Response(
            output,
            mimetype='text/csv',
            headers={'Content-Disposition': 'attachment; filename=products.csv'}
        ), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 400


@products_bp.route('/products/count', methods=['GET'])
@login_required
def get_product_count():
    try:
        count = Product.query.count()
        return jsonify({'count': count}), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 400


@products_bp.route('/products/summary', methods=['GET'])
@login_required
def get_product_summary():
    try:
        result = db.session.execute(text('CALL get_product_summary()')).fetchone()
        if result:
            summary = {
                '商品总数': result[0],
                '平均价': result[1],
                '最高价': result[2],
                '最低价': result[3]
            }
        else:
            summary = {
                '商品总数': 0,
                '平均价': 0,
                '最高价': 0,
                '最低价': 0
            }
        return jsonify(summary), 200
    except Exception as e:
        print(f"Error occurred: {e}")
        return jsonify({'error': str(e)}), 400
